<?php
/**
 * Created by PhpStorm.
 * User: peter
 * Date: 2018/11/7
 * Time: 4:03 PM
 */

namespace app\admin\logic;


use app\model\ActivityCollectionPoint;
use app\model\ActivityGroupStage;
use app\model\ActivityOrderAttach;
use extend\Functions;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class Export
{
    /**
     * @purpose导出保单
     * @param int $group_stage_id
     * @return \think\response\Download
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function insurance(int $group_stage_id)
    {
        $order_attach = new ActivityOrderAttach();
        $group_stage  = new ActivityGroupStage();
        $travel_info  = $order_attach
            ->alias('aoa')
            ->join('activity_order ao', 'aoa.order_id=ao.id')
            ->join('member m', 'ao.uid=m.uid')
            ->order('ao.pay_time asc')
            ->where([
                'aoa.group_stage_id' => $group_stage_id,
                'aoa.status'         => 99,
            ])
            ->field('aoa.fullname,aoa.travel_type,aoa.travel_sex,aoa.travel_mobile,
            aoa.travel_certificate_type,aoa.travel_certificate_number,m.nickname')
            ->select();

        $activity_info = $group_stage
            ->with(['activity' => function ($query) {
                $query->field('id,title');
            }])
            ->where('id', $group_stage_id)
            ->field('id,activity_id,FROM_UNIXTIME(start_time, "%Y-%m-%d") as start_date,adult_price,kid_price')
            ->find();
//        return $travel_info;
        $spreadsheet = new Spreadsheet();
        $sheet       = $spreadsheet->getActiveSheet();
        // 设置表头
        $sheet->setCellValue('A1', $activity_info->activity->title . '-团体保单');
        $sheet->mergeCells('A1:H1');
        // 设置行宽
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(10);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(15);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(15);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(25);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(12);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(15);

        // 固定示例
        $sheet->setCellValue('A2', '序号');
        $sheet->setCellValue('B2', '姓名');
        $sheet->setCellValue('C2', '证件类型(1:身份证,2:护照,99:其他)');
        $sheet->setCellValue('D2', '证件号码');
        $sheet->setCellValue('E2', '出生日期');
        $sheet->setCellValue('F2', '性别');
        $sheet->setCellValue('G2', '手机号码(非必须)');
        $sheet->setCellValue('H2', '昵称');

        $sheet->setCellValue('A3', 1);
        $sheet->setCellValue('B3', '张三');
        $sheet->setCellValue('C3', 1);
        $sheet->setCellValue('D3', '510112***********83');
        $sheet->setCellValue('E3', '1987-03-29');
        $sheet->setCellValue('F3', '男');
        $sheet->setCellValue('G3', '131*****888');
        $sheet->setCellValue('H3', '王二麻子');


        $sheet->setCellValue('A4', 2);
        $sheet->setCellValue('B4', '李四三');
        $sheet->setCellValue('C4', 2);
        $sheet->setCellValue('D4', '510112***********83');
        $sheet->setCellValue('E4', '1987-03-29');
        $sheet->setCellValue('F4', '女');
        $sheet->setCellValue('G4', '131*****666');
        $sheet->setCellValue('H4', '王二麻子');

        $sheet->setCellValue('A5', '序号');
        $sheet->setCellValue('B5', '姓名');
        $sheet->setCellValue('C5', '证件类型(1:身份证,2:护照,99:其他)');
        $sheet->setCellValue('D5', '证件号码');
        $sheet->setCellValue('E5', '出生日期');
        $sheet->setCellValue('F5', '性别');
        $sheet->setCellValue('G5', '手机号码(非必须)');
        $sheet->setCellValue('H5', '昵称');

        $start            = 5;
        $certificate_type = [
            1 => 1,
            2 => 99,
            3 => 99,
            4 => 99,
            5 => 2,
        ];

        $sex = [
            1 => '男',
            2 => '女',
        ];
        foreach ($travel_info as $key => $travel) {
            $start++;
            $sheet->setCellValue('A' . $start, $key + 1);
            $sheet->setCellValue('B' . $start, $travel['fullname']);
            $sheet->setCellValue('C' . $start, $certificate_type[$travel['travel_certificate_type']]);
            $sheet->setCellValue('D' . $start, '['.$travel['travel_certificate_number']);
            $sheet->setCellValue('E' . $start, '1987-03-29');
            $sheet->setCellValue('F' . $start, $sex[$travel['travel_sex']]);
            $sheet->setCellValue('G' . $start, $travel['travel_mobile']);
            $sheet->setCellValue('H' . $start, $travel['nickname']);
        }

        // 居中显示
        $alignment_center = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];
        $sheet->getStyle('A1')->applyFromArray($alignment_center);
        for ($i = 1; $i <= $start; $i++) {
            $sheet->getRowDimension($i)->setRowHeight(20);
        }
        $writer  = new Xlsx($spreadsheet);
        $content = null;
        ob_start();
        $writer->save('php://output');
        $content = ob_get_contents();
        ob_end_clean();
        return download($content, $activity_info->activity->title . '[保单信息].xlsx', true);
    }

    /**
     * @purpose 导出分车信息
     * @param int $group_stage_id
     * @return \think\response\Download
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function pointsInTheCar(int $group_stage_id)
    {
        $group_stage      = new ActivityGroupStage();
        $collection_point = new ActivityCollectionPoint();
        $travels          = $collection_point
            ->with(['orderAttach' => function ($query) {
                $query
                    ->alias('aoa')
                    ->where('aoa.status', 99)
                    ->join('activity_order ao', 'aoa.order_id=ao.id')
                    ->join('member m', 'ao.uid=m.uid')
                    ->order('ao.pay_time asc')
                    ->field('aoa.id,aoa.collection_point_id,aoa.fullname,aoa.travel_type,aoa.travel_sex,
                    aoa.travel_mobile,aoa.travel_certificate_type,aoa.travel_certificate_number,m.nickname,m.username,m.mobile,
                    FROM_UNIXTIME(ao.pay_time, "%Y-%m-%d %H:%i:%s") as pay_date');
            }])
            ->where('activity_group_stage_id', $group_stage_id)
            ->field('id,city_name,title,address')
            ->select()
            ->toArray();
        $activity_info    = $group_stage
            ->with(['activity' => function ($query) {
                $query->field('id,title');
            }])
            ->where('id', $group_stage_id)
            ->field('id,activity_id,FROM_UNIXTIME(start_time, "%Y-%m-%d") as start_date,adult_price,kid_price')
            ->find();
        $spreadsheet      = new Spreadsheet();
        $sheet            = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', $activity_info->activity->title);
        $sheet->setCellValue('A2', '分车信息：');
        $sheet->setCellValue('A3', '车牌号：');
        $sheet->setCellValue('A4', '义务召集：');
        // 合并单元格
        $sheet->mergeCells('A1:H1');
        $sheet->mergeCells('A2:H2');
        $sheet->mergeCells('A3:H3');
        $sheet->mergeCells('A4:H4');
        // 设置列宽
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(15);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(15);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(25);
        // 水平居中对齐
        $alignment_horizontal_center = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        // 垂直居中对齐
        $alignment_vertical_center = [
            'alignment' => [
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];
        $start                     = 4;
        foreach ($travels as $key => $travel) {
            ;
            $start++;
            $sheet->setCellValue('A' . $start, $travel['title'] ? $travel['title'] : $travel['address']);
            $sheet->getStyle('A' . $start)->applyFromArray($alignment_horizontal_center);
            $sheet->mergeCells('A' . $start . ':H' . $start);
            foreach ($travel['order_attach'] as $k => $attach) {
                $start++;
                $sheet->setCellValue('A' . $start, $k + 1);
                $sheet->setCellValue('B' . $start, $attach['nickname'] ? $attach['nickname'] : $attach['username']);
                $sheet->setCellValue('C' . $start, Functions::hideNameSecond($attach['fullname']));
                $sheet->setCellValue('D' . $start, $attach['fullname']);
                $sheet->setCellValue('E' . $start, (string)$attach['travel_mobile']);
                $sheet->setCellValue('F' . $start, $attach['pay_date']);
            }
        }
        for ($i = 1; $i <= $start; $i++) {
            $sheet->getRowDimension($i)->setRowHeight(25);
        }
        // 设置区域内内容垂直对齐
        $sheet->getStyle('A1:H' . $start)->applyFromArray($alignment_vertical_center);
        $writer  = new Xlsx($spreadsheet);
        $content = null;
        ob_start();
        $writer->save('php://output');
        $content = ob_get_contents();
        ob_end_clean();
        return download($content, $activity_info->activity->title . '[分车信息].xlsx', true);
    }

    /**
     * @purpose 导出出行人
     * @param int $group_stage_id
     * @return \think\response\Download
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function travel(int $group_stage_id)
    {
        $group_stage      = new ActivityGroupStage();
        $collection_point = new ActivityCollectionPoint();
        $travels          = $collection_point
            ->with(['orderAttach' => function ($query) {
                $query
                    ->alias('aoa')
                    ->where('aoa.status', 99)
                    ->join('activity_order ao', 'aoa.order_id=ao.id')
                    ->join('member m', 'ao.uid=m.uid')
                    ->order('ao.pay_time asc')
                    ->field('aoa.id,aoa.collection_point_id,aoa.fullname,aoa.travel_type,aoa.travel_sex,
                    aoa.travel_mobile,aoa.travel_certificate_type,aoa.travel_certificate_number,m.nickname,m.username,m.mobile,
                    FROM_UNIXTIME(ao.pay_time, "%Y-%m-%d %H:%i:%s") as pay_date');
            }])
            ->where('activity_group_stage_id', $group_stage_id)
            ->field('id,city_name,title,address')
            ->select()
            ->toArray();
        $activity_info    = $group_stage
            ->with(['activity' => function ($query) {
                $query->field('id,title');
            }])
            ->where('id', $group_stage_id)
            ->field('id,activity_id,FROM_UNIXTIME(start_time, "%Y-%m-%d") as start_date,adult_price,kid_price')
            ->find();
        $spreadsheet      = new Spreadsheet();
        $sheet            = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', '活动名称: ' . $activity_info->activity->title);
        $sheet->setCellValue('A2', '出行日期: ' . $activity_info->start_date);
        $sheet->setCellValue('A3', '成人价: ' . ($activity_info->adult_price / 100) . '元');
        $sheet->setCellValue('A4', '儿童价: ' . ($activity_info->kid_price / 100) . '元');
        // 合并单元格
        $sheet->mergeCells('A1:H1');
        $sheet->mergeCells('A2:H2');
        $sheet->mergeCells('A3:H3');
        $sheet->mergeCells('A4:H4');
        // 设置列宽
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(10);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(10);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(10);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(15);
        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(20);
        // 水平居中对齐
        $alignment_horizontal_center = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        // 垂直居中对齐
        $alignment_vertical_center = [
            'alignment' => [
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];
        $sex                       = [
            1 => '男',
            2 => '女',
        ];
        $type                      = [
            1 => '成人',
            2 => '小孩',
        ];
        $start                     = 4;
        foreach ($travels as $key => $travel) {
            ;
            $start++;
            $sheet->setCellValue('A' . $start, $travel['title'] ? $travel['title'] : $travel['address']);
            $sheet->getStyle('A' . $start)->applyFromArray($alignment_horizontal_center);
            $sheet->mergeCells('A' . $start . ':H' . $start);
            foreach ($travel['order_attach'] as $k => $attach) {
                $start++;
                $sheet->getCell('E' . $start)->setDataType('s');
                $sheet->getCell('F' . $start)->setDataType('s');
                $sheet->setCellValue('A' . $start, $k + 1);
                $sheet->setCellValue('B' . $start, $attach['fullname']);
                $sheet->setCellValue('C' . $start, $sex[$attach['travel_sex']]);
                $sheet->setCellValue('D' . $start, $type[$attach['travel_type']]);
                $sheet->setCellValue('E' . $start, "[" . (string)$attach['travel_certificate_number']);
                $sheet->setCellValue('F' . $start, (string)$attach['travel_mobile']);
                $sheet->setCellValue('G' . $start, $attach['nickname'] ? $attach['nickname'] : $attach['username']);
                $sheet->setCellValue('H' . $start, $attach['pay_date']);
            }
        }
        for ($i = 1; $i <= $start; $i++) {
            $sheet->getRowDimension($i)->setRowHeight(25);
        }
        // 设置区域内内容垂直对齐
        $sheet->getStyle('A1:H' . $start)->applyFromArray($alignment_vertical_center);
        $writer  = new Xlsx($spreadsheet);
        $content = null;
        ob_start();
        $writer->save('php://output');
        $content = ob_get_contents();
        ob_end_clean();
        return download($content, $activity_info->activity->title . '[出行人信息].xlsx', true);
    }
}